# -*- coding: utf-8 -*-

"""A basic example of using the association object pattern.

The association object pattern is a richer form of a many-to-many
relationship.

The model will be an ecommerce example.  We will have an Order, which
represents a set of Items purchased by a user.  Each Item has a price.
However, the Order must store its own price for each Item, representing
the price paid by the user for that particular order, which is independent
of the price on each Item (since those can change).
"""

#import logging
from datetime import datetime

from sqlalchemy import *

from sqlalchemy.orm import *




import time, random, md5


from tables import *

def uuid( *args ):
  """
    Generates a universally unique ID.
    Any arguments only create more randomness.
  """
  t = long( time.time() * 1000 )
  r = long( random.random()*100000000000000000L )
  try:
    a = socket.gethostbyname( socket.gethostname() )
  except:
    # if we can't get a network address, just imagine one
    a = random.random()*100000000000000000L
  data = str(t)+' '+str(r)+' '+str(a)+' '+str(args)
  data = md5.md5(data).hexdigest()
  return data


"""

def item(name):
    return session.query(Item).filter_by(description=name).one()

"""

# Uncomment these to watch database activity.
#logging.basicConfig(format='%(message)s')
#logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)


class DB:

    def __init__(self, echo = False):
        print "create engine"
        uri = 'sqlite:///data.dat'
        #uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/vpipe?charset=utf8&use_unicode=0'
        self.engine = create_engine(uri,encoding='utf-8')
        metadata = MetaData(self.engine)
        metadata.bind.echo  = echo



        columns = Table('aid_columns', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(60), nullable=False),
            Column('table_', String(60), nullable=False),
            Column('col_name', String(60), nullable=False),
            Column('keycol', SmallInteger, nullable=False),
            Column('dir', SmallInteger, nullable=False),
            Column('published', SmallInteger, nullable=False, default=1),
            Column('createdon', DateTime, nullable=False, default=datetime.now())
            )

        
        mappings = Table('aid_mapping', metadata,
            Column('uid', String(32), primary_key=True, default = uuid()),
            Column('name', String(30), nullable=False),
            Column('col_list', String(2000), nullable=False),
            Column('mapping', String(2000), nullable=False),
            Column('start_row', Integer, nullable=False),
            Column('start_col', Integer, nullable=False),
            Column('ignore_last', Integer, nullable=False),
            Column('createdby', String(60), nullable=False),
            Column('createdon', String(30), nullable=False),
            Column('key_col', String(30), nullable=False),
            Column('access', SmallInteger, nullable=False, default=1),
            Column('mappingtype', String(30), nullable=False)            
            )

        files = Table('aid_files', metadata,
            Column('uid', String(32), primary_key=True, default = uuid()),
            Column('filename', String(60), nullable=False),
            Column('fsize', Integer, nullable=True),            
            Column('mtime', DateTime, nullable=True),       
            Column('ctime', DateTime, nullable=True),             
            Column('map_uid', String(32), ForeignKey('aid_mapping.uid')),
            Column('period', SmallInteger, nullable=False, default=0),
            Column('pstart', Date, nullable=False, default='0000-00-00'),
            Column('pend', Date, nullable=False, default='0000-00-00'),
            Column('inout_date', Date, nullable=False, default='0000-00-00'),
            Column('createdby', String(60), nullable=True),
            Column('createdon', DateTime, nullable=False, default=datetime.now())            
            )        

        employees = Table('aid_employees', metadata, 
            #Column('id', Integer, primary_key=True),
            Column('uid', String(32), primary_key=True, default=uuid()),
            Column('name', String(60), nullable=False),
            Column('workercode', String(60), nullable=True),
            Column('idcard', String(60), nullable=True),
            Column('department', String(60), nullable=True),            
            Column('createdon', DateTime, nullable=False, default=datetime.now()),
            Column('access', SmallInteger, nullable=True, default=1),
            Column('oid', Integer, nullable=True)            
            )

        

        items = Table('aid_items', metadata,
            Column('id', Integer, primary_key=True),
            Column('file_uid', String(32), ForeignKey('aid_files.uid')),
            Column('employee_uid', String(32), ForeignKey('aid_employees.uid')),
            Column('colname', String(60),  nullable=False),
            Column('value', Numeric(8, 2), nullable=False),
            Column('row_no', Integer, nullable=True),
            Column('col', Integer, nullable=True),
            Column('createdon', DateTime, nullable=False, default=datetime.now())
            
            )
        
        metadata.create_all()

        mapper(ItemColumn, columns)
        
        mapper(Mapping, mappings)
        
        mapper(ExcelFile, files)
        
        mapper(Employee, employees)
        
        mapper(Item, items)
        
        """
        mapper(Employee, employees, 
            properties={'aid_items': 
                    relation(Item, cascade="all, delete-orphan", backref='employee')
                })
        
        """
        """
        mapper(Employee, employees, properties={
            'items': relation(Item, cascade="all, delete-orphan",
                                    backref='aid_employees')
        })

        

        
        
        
        
        mapper(Item, items, properties={
            'item': relation(Item, lazy=False)
        })
        """

        # function to return items from the DB
            
        #item('SA Mug')    

        #connection = engine.connect()
        #result = connection.execute("select * from items")
        self.session = create_session()
        print "end db init..."
    
    def execute(self, sql):
        connection = self.engine.connect()
        result = connection.execute(sql)
        #for row in result:
        #    print row['name'].encode('utf8')
        return result
    
    def getMapping(self, map_uid):
        ics = None
        try:            
            ics = self.session.query(Mapping).filter_by(uid=map_uid).one()
        except:
            pass
        return ics

    def setMapping(self, mapping):
        
        try:
            self.session.save(mapping)
            self.session.flush()
            self.session.clear()
            state = file.uid
        except:
            state = 0
        return state


    def getMaps(self):
        maps = self.session.query(Mapping)
        return maps

    def getFile(self, fname):
        try:
            file = self.session.query(File).filter_by(filename = fname).one()
        except:
            file = None
        return file

    def getColNames(self):
        
        ics = self.session.query(ItemColumn)#.get(1)#.filter_by(description=name).one()
        return ics
        
    def setEmp(self, employee):
        
        try:
            self.session.save(employee)
            self.session.flush()
            self.session.clear()
            state = 1
        except:
            state = 0
        return state


    def setFile(self, file):
        
        try:
            self.session.save(file)
            self.session.flush()
            self.session.clear()
            state = file.uid
        except:
            state = 0
            
        print "file uid: "+file.uid
        return state
        
        # create our catalog
        """
        session.save(Item('SA T-Shirt', 10.99))
        session.save(Item('SA Mug', 6.50))
        session.save(Item('SA Hat', 8.99))
        session.save(Item('MySQL Crowbar', 16.99))
        session.flush()


        for row in result:
            print row['description']

        # create an order
        order = Order('john smith')

        # add three OrderItem associations to the Order and save

        order.order_items.append(OrderItem(item('SA Mug')))
        order.order_items.append(OrderItem(item('MySQL Crowbar'), 10.99))
        order.order_items.append(OrderItem(item('SA Hat')))
        session.save(order)
        session.flush()

        session.clear()

        # query the order, print items
        order = session.query(Order).filter_by(customer_name='john smith').one()
        print [(order_item.item.description, order_item.price) 
               for order_item in order.order_items]

        # print customers who bought 'MySQL Crowbar' on sale
        q = session.query(Order).join(['order_items', 'item'])
        q = q.filter(and_(Item.description == 'MySQL Crowbar',
                          Item.price > OrderItem.price))

        print [order.customer_name for order in q]
        """



def test():
  db = DB(True)
  ######################################################
  #sfile = db.getFile('F:\\dev\\excel\\salary2.xls')
  #print sfile.filename
  #print sfile.createdon
  ######################################################
  """
  file = ExcelFile()
  file.map_uid ='uid'
  file.filename ='file01.xls'
  db.setFile(file)
  """
  ######################################################
  #cns = db.getColNames()
  """
  map = db.getMapping('')
  if map:
    print map.uid
  """
  ######################################################
  #sql = "select * from aid_mapping limit 0, 5"
  #db.execute(sql)
  ######################################################

if __name__ == "__main__":
    test()
